# coding = utf-8
import json

import pandas as pd
import numpy as np
from django.db.models.functions import ExtractYear, ExtractMonth

from django.db.models import Sum, Case, When, FloatField, F

from rest_framework.response import Response
from rest_framework.views import APIView

from building.models import Project, Deposit, BidBusiness


class ProjectMonthStatsAPIView(APIView):
    """工程登记月统计"""
    authentication_classes = ()

    def get(self, request, *args, **kwargs):
        # 月统计
        year = self.request.query_params.get("year", None)

        results = Project.objects.filter(open_time__year=year)\
            .annotate(year=ExtractYear("open_time"), month=ExtractMonth("open_time"))\
            .values("year", "month")\
            .annotate(
                quali_recv_totals=Sum("quali_recv_fee"),
                quali_pay_totals=Sum("quali_pay_fee"),
                bid_costs=Sum(Case(When(is_win=1, then=F("costs")), default=0, output_field=FloatField())),
                direct_costs=Sum(Case(When(is_win=3, then=F("costs")), default=0, output_field=FloatField())),
                costs=Sum("costs")
            ).order_by("year", "month")

        df = pd.DataFrame()
        if results.exists():
            df = pd.DataFrame(list(results))
            df = df.loc[:, ['month', 'bid_costs', 'direct_costs']]
            df.set_index('month', inplace=True)

        series = pd.Series(np.arange(1, 13))
        df_month = pd.DataFrame(np.zeros((12, 3), dtype=int), columns=["month", "bid_costs", "direct_costs"])
        df_month["month"] = series

        if len(df.index) > 0:
            df_month.set_index('month', inplace=True)
            df_month = df_month + df
            df_month.reset_index(inplace=True)
            df_month.fillna(0, inplace=True)

        df_month["month"] = df_month["month"].astype("str") + '月'
        df_month.rename(columns={"month": "月份", "bid_costs": "中标工程造价", "direct_costs": "直签工程造价"}, inplace=True)

        option = {
            "title": {
                "text": '{}年直签工程造价和中标工程造价对比图'.format(year),
                "top": 5,
            },
            "tooltip": {
                "trigger": 'axis'
            },
            "grid": {
                "left": '4%',
                "right": '3%',
                "bottom": '4%',
                "containLabel": True
            },
            "legend": {
                "data": ["中标工程造价", "直签工程造价"],
                "top": 5,
            },
            "toolbox": {
                "show": True,
                "feature": {
                    "dataView": {"show": True, "readOnly": False},
                    "magicType": {"show": True, "type": ['line', 'bar']},
                    "restore": {"show": True},
                    "saveAsImage": {"show": True}
                }
            },
            # calculable: true,
            "xAxis": [
                {
                    "type": "category",
                    "data": df_month["月份"].tolist(),
                    "axisTick": {
                        "alignWithLabel": True
                    }
                }
            ],
            "yAxis": [
                {
                    "type": "value",
                }
            ],
            "series": [
                {
                    "name": "中标工程造价",
                    "type": "bar",
                    "barWidth": '25%',
                    "data": df_month["中标工程造价"].tolist(),

                },
                {
                    "name": '直签工程造价',
                    "type": 'bar',
                    "barWidth": '25%',
                    "data": df_month["直签工程造价"].tolist(),

                }
            ]
        }
        datas = {
            "results": results,
            "option": option
        }
        # print(option)
        return Response(datas)


class ProjectYearStatsAPIView(APIView):
    """工程登记年统计"""
    authentication_classes = ()

    def get(self, request, *args, **kwargs):
        # 年统计
        start_year = self.request.query_params.get("start_year", None)
        end_year = self.request.query_params.get("end_year", None)
        istart_year = int(start_year)
        iend_year = int(end_year)
        results = Project.objects.filter(open_time__year__range=[istart_year, iend_year])\
            .annotate(year=ExtractYear("open_time")).values("year")\
            .annotate(
                bid_costs=Sum(Case(When(is_win=1, then=F("costs")), default=0, output_field=FloatField())),
                direct_costs=Sum(Case(When(is_win=3, then=F("costs")), default=0, output_field=FloatField())),
                costs=Sum("costs")
            ).order_by("year")

        df = pd.DataFrame()
        if results.exists():
            df = pd.DataFrame(list(results))
            df = df.loc[:, ['year', 'bid_costs', 'direct_costs', 'costs']]
            df.set_index('year', inplace=True)

        series = pd.Series(np.arange(istart_year, iend_year + 1))
        num = series.count()
        df_year = pd.DataFrame(np.zeros((num, 4), dtype=int), columns=['year', 'bid_costs', 'direct_costs', 'costs'])
        df_year["year"] = series

        if len(df.index) > 0:
            df_year.set_index('year', inplace=True)
            df_year = df_year + df
            df_year.reset_index(inplace=True)
            df_year.fillna(0, inplace=True)

        df_year["year"] = df_year["year"].astype("str") + '年'
        df_year.rename(columns={"year": "年份", "bid_costs": "中标工程造价", "direct_costs": "直签工程造价", "costs": "工程造价"}, inplace=True)
        # print(df_year)

        option = {
            "title": {
                "text": '{}年至{}年工程造价走势图'.format(start_year, end_year),
                "top": 5,
            },
            "tooltip": {
                "trigger": 'axis'
            },
            "grid": {
                "left": '3%',
                "right": '4%',
                "bottom": '3%',
                "containLabel": True
            },
            "legend": {
                "data": ["工程造价"],
                "top": 5,
            },
            "toolbox": {
                "show": True,
                "feature": {
                    "dataView": {"show": True, "readOnly": False},
                    "magicType": {"show": True, "type": ['line', 'bar']},
                    "restore": {"show": True},
                    "saveAsImage": {"show": True}
                }
            },
            "xAxis": [
                {
                    "type": "category",
                    "data": df_year["年份"].tolist(),
                    "axisTick": {
                        "alignWithLabel": True
                    }
                }
            ],
            "yAxis": [
                {
                    "type": "value",
                }
            ],
            "series": [
                {
                    "name": "工程造价",
                    "type": "bar",
                    "barWidth": '40',
                    # "barMaxWidth": "65",
                    "data": df_year["工程造价"].tolist(),
                }
            ]
        }

        return Response(option)


class DepositMonthStatsAPIView(APIView):
    """保证金月统计"""
    authentication_classes = ()

    def get(self, request, *args, **kwargs):
        # 月统计
        year = self.request.query_params.get("year", None)
        # 保证金
        pay_results = Deposit.objects.filter(pay_date__year=year)\
            .annotate(year=ExtractYear("pay_date"), month=ExtractMonth("pay_date"))\
            .values("year", "month").annotate(pay_money_totals=Sum("pay_money"), refund_money_totals=Sum(0)).order_by("year", "month")

        refund_results = Deposit.objects.filter(refund_date__year=year)\
            .annotate(year=ExtractYear("refund_date"), month=ExtractMonth("refund_date"))\
            .values("year", "month").annotate(pay_money_totals=Sum(0), refund_money_totals=Sum("refund_money")).order_by("year", "month")

        df_pay = pd.DataFrame(list(pay_results))
        df_refund = pd.DataFrame(list(refund_results))
        result = pd.concat([df_pay, df_refund])
        if result.empty:
            return Response(list())

        # result.loc[:, "year"] = result["year"].astype("str") + "-" + result["month"].astype("str")
        # result = result.loc[:, ["year", "pay_money_totals", "refund_money_totals"]]

        result = result.groupby(["year", "month"], as_index=False).sum()
        result = result.to_json(orient="records")
        parsed_result = json.loads(result)
        return Response(parsed_result)


class BidBusinessStatsAPIView(APIView):
    """投标业务月统计"""
    authentication_classes = ()

    def get(self, request, *args, **kwargs):
        # 月统计
        year = self.request.query_params.get("year", None)

        results = BidBusiness.objects.filter(bid_date__year=year)\
            .annotate(year=ExtractYear("bid_date"), month=ExtractMonth("bid_date"))\
            .values("year", "month").annotate(fees_totals=Sum("fees"), broker_fees_totals=Sum("broker_fees")).order_by("year", "month")

        return Response(results)

